package servicios.biziGasteizAdmin;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ListIterator;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.tomcat.util.http.fileupload.FileUtils;
import org.json.JSONArray;
import org.json.JSONObject;

import connection.BDConnection;
import connection.Geocoding;

/**
 * Servlet implementation class PlacesServices
 */
public class PlacesServices extends HttpServlet {
	private static final long serialVersionUID = 1L;
    private String CONTENT_TYPE = "text/json";
    private Geocoding geocode;
    /**
     * @see HttpServlet#HttpServlet()
     */
    public PlacesServices() {
        super();
        this.geocode = new Geocoding();
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		try {
			processRequest(request, response);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		try {
			boolean isMultipart = ServletFileUpload.isMultipartContent(request);
			if(!isMultipart)
				processRequest(request, response);
			else
				proccessMultiparRequest(request, response);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
		}
	}
	
	
	private void processRequest(HttpServletRequest request, HttpServletResponse  response) throws SQLException, IOException, ClassNotFoundException{
		response.setContentType(CONTENT_TYPE);
		String requestP = request.getParameter("request");
		if(requestP.compareToIgnoreCase("GETCOMPONENTS") == 0){
			getComponents(request, response);
		}
		else if(requestP.compareToIgnoreCase("DELETEROW") == 0){
			deleteRow(request, response);
		}
	}
	
	private void proccessMultiparRequest(HttpServletRequest request, HttpServletResponse response) throws IOException, ClassNotFoundException{
		response.setContentType("text/html");
		String requestP = request.getParameter("request");
		String setFile = request.getParameter("setfile");
		if(requestP.compareToIgnoreCase("NEWPLACE") == 0){
			newPlace(request, response);
		}
		else if(requestP.compareToIgnoreCase("UPDATEPLACE") == 0 && setFile.equals("true")){
			updatePlaceAndFoto(request, response);
		}
		else if(requestP.compareToIgnoreCase("UPDATEPLACE") == 0 && setFile.equals("false")){
			updatePlace(request, response);
		}
	        
	}
	
	private void updatePlace(HttpServletRequest request, HttpServletResponse response) throws IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			List items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
			ListIterator iterator = items.listIterator();
			String sqlStr = "UPDATE LUGARES SET ";
			String codigo = "";
	        while (iterator.hasNext()) {
	        	FileItem item = (FileItem) iterator.next();
	            if (item.isFormField()) {
	                String fieldname = item.getFieldName();
	                String fieldvalue = item.getString();
	                if(fieldname.equals("codigo")){
	                	codigo = fieldvalue;
	                }
		            else if(fieldname.equals("calle")){
	                	sqlStr += "calle='"+fieldvalue+"',";
	                	String geometry = this.geocode.geocodeStreet(fieldvalue);
	                	sqlStr += "the_geom="+geometry+",";;
	                }
	                else{
	                	sqlStr += fieldname+"='"+fieldvalue+"',";
	                }
	            } 
	        }
	        
	        sqlStr = sqlStr.substring(0, sqlStr.length() -1);
	        sqlStr += " WHERE codigo='"+codigo+"'";
	        
	        JSONObject resJson = new JSONObject();
	        int rs = bdCon.executeUpdate(sqlStr);
	        if(rs > 0){
				resJson.put("success", true);
			}
			else{
				resJson.put("success", false);
			}
			response.getWriter().write(resJson.toString());
			bdCon.closeConnection();
        
		}catch(Exception o){
			o.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+o.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	
	private void updatePlaceAndFoto(HttpServletRequest request, HttpServletResponse response) throws IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			List items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
			ListIterator iterator = items.listIterator();
			String sqlStr = "UPDATE LUGARES SET ";
			String codigo = "";
	        while (iterator.hasNext()) {
	        	FileItem item = (FileItem) iterator.next();
	            if (item.isFormField()) {
	                String fieldname = item.getFieldName();
	                String fieldvalue = item.getString();
	                if(fieldname.equals("codigo")){
	                	codigo = fieldvalue;
	                }else if(fieldname.equals("calle")){
	                	sqlStr += "calle='"+fieldvalue+"',";
	                	String geometry = this.geocode.geocodeStreet(fieldvalue);
	                	sqlStr += "the_geom="+geometry+",";;
	                }
	                else{
	                	sqlStr += fieldname+"='"+fieldvalue+"',";
	                }
	            } else {
	            	
	                String fieldname = item.getFieldName();
	                String filename = item.getName();
	                sqlStr += fieldname+"='/images/lugares/"+filename+"',";
	                
	                InputStream filecontent = item.getInputStream();
	                String relativePath = request.getSession().getServletContext().getRealPath("/images/lugares");
	                File f=new File(relativePath+File.separator+filename);//Aqui le dan el nombre y/o con la ruta del archivo salida
	                OutputStream os=new FileOutputStream(f);
	                byte[] buf =new byte[1024];//Actualizado me olvide del 1024
	                int len;
	                while((len=filecontent.read(buf))>0){
	                	os.write(buf,0,len);
	                }
	                os.close();
	                filecontent.close();
	            }
	        }
	        
	        sqlStr = sqlStr.substring(0, sqlStr.length() -1);
	        sqlStr += " WHERE codigo='"+codigo+"'";
	        
	        JSONObject resJson = new JSONObject();
	        int rs = bdCon.executeUpdate(sqlStr);
	        if(rs > 0){
				resJson.put("success", true);
			}
			else{
				resJson.put("success", false);
			}
			response.getWriter().write(resJson.toString());
			bdCon.closeConnection();
	        
		}catch(Exception o){
			o.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+o.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	private void newPlace(HttpServletRequest request, HttpServletResponse response) throws IOException, ClassNotFoundException{
		
		String values = "";
		String columns= "";
		String sqlQuery = "";
		BDConnection bdCon = new BDConnection();
		try{
			List items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
			ListIterator iterator = items.listIterator();
	        while (iterator.hasNext()) {
	        	FileItem item = (FileItem) iterator.next();
	            if (item.isFormField()) {
	                String fieldname = item.getFieldName();
	                String fieldvalue = item.getString();
	                if(fieldname.equals("calle")){
	                	values = values + "'"+fieldvalue+"'" +",";
	                	columns = columns + fieldname +",";
	                	String geometry = this.geocode.geocodeStreet(fieldvalue);
	                	if(geometry != null){
	                		values += geometry+",";
	                		columns += "the_geom,";
	                	}
	                	else{
	                		response.getWriter().print("{\"success\":false, \"data\": \"Street not geocoded\"}");
	            			bdCon.closeConnection();
	                	}
	                }
	                else if(!fieldname.equals("request") || !fieldname.equals("codigo")){
	                	values = values + "'"+fieldvalue+"'" +",";
	                	columns = columns + fieldname +",";
	                }
	                
	            } else {
	                String filename = item.getName();
	                InputStream filecontent = item.getInputStream();
	                values = values + "'/images/lugares/"+filename+"',";
	                columns = columns + "foto,";
	                String relativePath = request.getSession().getServletContext().getRealPath("/images/lugares");
	                File f=new File(relativePath+File.separator+filename);//Aqui le dan el nombre y/o con la ruta del archivo salida
	                OutputStream os=new FileOutputStream(f);
	                byte[] buf =new byte[1024];//Actualizado me olvide del 1024
	                int len;
	                while((len=filecontent.read(buf))>0){
	                	os.write(buf,0,len);
	                }
	                os.close();
	                filecontent.close();
	            }
	        }
        	columns = columns.substring(0, columns.length() -1);
        	values = values.substring(0, values.length() -1);
        	sqlQuery = "INSERT INTO LUGARES ("+columns+") VALUES ("+values+")";
        	System.out.println(sqlQuery);
        	JSONObject resJson = new JSONObject();
	        int rs = bdCon.executeUpdate(sqlQuery);
	        if(rs > 0){
				resJson.put("success", true);
			}
			else{
				resJson.put("success", false);
			}
			response.getWriter().write(resJson.toString());
			bdCon.closeConnection();
	        
		}catch(Exception o){
			o.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+o.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	
	private void deleteRow(HttpServletRequest request, HttpServletResponse response) throws IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			String codigo = request.getParameter("codigo");
			String sqlStr = "DELETE FROM LUGARES WHERE CODIGO = '"+codigo+"'";
			
			JSONObject resJson = new JSONObject();
			int rs = bdCon.executeUpdate(sqlStr);
			if(rs > 0){
				resJson.put("success", true);
			}
			else{
				resJson.put("success", false);
			}
			response.getWriter().write(resJson.toString());
			bdCon.closeConnection();
		}
		catch(Exception e){
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	private void getComponents(HttpServletRequest request, HttpServletResponse response) throws IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			String sqlStr = "SELECT nombre, codigo,calle,enlace,telefono,foto,descripcion," +
					"ST_ASTEXT(the_geom) as localizacion FROM LUGARES";
			ResultSet rs = bdCon.executeQuery(sqlStr);
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			JSONArray itemsArray = new JSONArray(); 
			while(rs.next()){
				JSONObject item = new JSONObject();
				item.put("nombre", rs.getString("nombre"));
				item.put("codigo", rs.getString("codigo"));
				item.put("calle", rs.getString("calle"));
				item.put("enlace", rs.getString("enlace"));
				item.put("telefono", rs.getString("telefono"));
				item.put("foto", rs.getString("foto"));
				item.put("descripcion", rs.getString("descripcion"));
				itemsArray.put(item);
			}
			resJson.put("data", itemsArray);
			
			response.getWriter().write(resJson.toString());
			
			bdCon.closeConnection(rs);
		}
		catch(Exception e){
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
}
